{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to prepare the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use a simplified version of the data set constructed in Chapter 4, Alpha factor research. It consists of daily stock prices provided by Quandl for the 2010-2017 period and various engineered features. \n",
    "\n",
    "The decision tree models in this chapter are not equipped to handle missing or categorical variables, so we will apply dummy encoding to the latter after dropping any of the former."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:33.669796Z",
     "start_time": "2018-11-09T16:12:33.468906Z"
    }
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "import warnings\n",
    "import os\n",
    "from pathlib import Path\n",
    "import quandl\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import graphviz\n",
    "from sklearn.tree import DecisionTreeClassifier, DecisionTreeRegressor, export_graphviz, _tree\n",
    "from sklearn.linear_model import LinearRegression, Ridge, LogisticRegression\n",
    "from sklearn.model_selection import train_test_split, cross_val_score, GridSearchCV\n",
    "from sklearn.metrics import roc_auc_score, roc_curve, mean_squared_error, precision_recall_curve\n",
    "from sklearn.preprocessing import Imputer\n",
    "import statsmodels.api as sm\n",
    "from scipy.interpolate import interp1d, interp2d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:33.673698Z",
     "start_time": "2018-11-09T16:12:33.671186Z"
    }
   },
   "outputs": [],
   "source": [
    "warnings.filterwarnings('ignore')\n",
    "plt.style.use('ggplot')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:37.787049Z",
     "start_time": "2018-11-09T16:12:33.675024Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.io.pytables.HDFStore'>\n",
      "File path: ../data/assets.h5\n",
      "/engineered_features            frame        (shape->[445640,33])  \n",
      "/fred/assets                    frame        (shape->[4826,5])     \n",
      "/quandl/wiki/prices             frame        (shape->[15389314,12])\n",
      "/quandl/wiki/stocks             frame        (shape->[1,2])        \n",
      "/sp500/prices                   frame        (shape->[37721,5])    \n",
      "/sp500/stocks                   frame        (shape->[1,7])        \n",
      "/us_equities/stocks             frame        (shape->[6834,6])     \n"
     ]
    }
   ],
   "source": [
    "with pd.HDFStore('../data/assets.h5') as store:\n",
    "    print(store.info())\n",
    "    prices = store['quandl/wiki/prices'].adj_close.unstack('ticker')\n",
    "    stocks = store['us_equities/stocks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:37.806190Z",
     "start_time": "2018-11-09T16:12:37.788666Z"
    }
   },
   "outputs": [],
   "source": [
    "shared = prices.columns.intersection(stocks.index)\n",
    "prices = prices.loc['2010': '2018', shared]\n",
    "stocks = stocks.loc[shared, ['marketcap', 'ipoyear', 'sector']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:37.812849Z",
     "start_time": "2018-11-09T16:12:37.807928Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2412 entries, A to ZUMZ\n",
      "Data columns (total 3 columns):\n",
      "marketcap    2407 non-null float64\n",
      "ipoyear      1065 non-null float64\n",
      "sector       2372 non-null object\n",
      "dtypes: float64(2), object(1)\n",
      "memory usage: 75.4+ KB\n"
     ]
    }
   ],
   "source": [
    "stocks.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:37.884662Z",
     "start_time": "2018-11-09T16:12:37.813852Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 2113 entries, 2010-01-04 to 2018-03-27\n",
      "Columns: 2412 entries, A to ZUMZ\n",
      "dtypes: float64(2412)\n",
      "memory usage: 38.9 MB\n"
     ]
    }
   ],
   "source": [
    "prices.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create monthly return series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:37.918524Z",
     "start_time": "2018-11-09T16:12:37.885693Z"
    }
   },
   "outputs": [],
   "source": [
    "returns = prices.resample('M').last().pct_change().stack().swaplevel()\n",
    "returns = (returns[returns.between(left=returns.quantile(.05), \n",
    "                                   right=returns.quantile(.95))].to_frame('returns'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lagged Returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.090053Z",
     "start_time": "2018-11-09T16:12:37.919593Z"
    }
   },
   "outputs": [],
   "source": [
    "for t in range(1, 13):\n",
    "    returns[f't-{t}'] = returns.groupby(level='ticker').returns.shift(t)\n",
    "returns = returns.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time Period Dummies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.143960Z",
     "start_time": "2018-11-09T16:12:38.091096Z"
    }
   },
   "outputs": [],
   "source": [
    "# returns = returns.reset_index('date')\n",
    "dates = returns.index.get_level_values('date')\n",
    "returns['year'] = dates.year\n",
    "returns['month'] = dates.month\n",
    "returns = pd.get_dummies(returns, columns=['year', 'month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.211032Z",
     "start_time": "2018-11-09T16:12:38.144952Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "MultiIndex: 171162 entries, (AAON, 2011-02-28 00:00:00) to (ZTS, 2018-03-31 00:00:00)\n",
      "Data columns (total 33 columns):\n",
      "returns      171162 non-null float64\n",
      "t-1          171162 non-null float64\n",
      "t-2          171162 non-null float64\n",
      "t-3          171162 non-null float64\n",
      "t-4          171162 non-null float64\n",
      "t-5          171162 non-null float64\n",
      "t-6          171162 non-null float64\n",
      "t-7          171162 non-null float64\n",
      "t-8          171162 non-null float64\n",
      "t-9          171162 non-null float64\n",
      "t-10         171162 non-null float64\n",
      "t-11         171162 non-null float64\n",
      "t-12         171162 non-null float64\n",
      "year_2011    171162 non-null uint8\n",
      "year_2012    171162 non-null uint8\n",
      "year_2013    171162 non-null uint8\n",
      "year_2014    171162 non-null uint8\n",
      "year_2015    171162 non-null uint8\n",
      "year_2016    171162 non-null uint8\n",
      "year_2017    171162 non-null uint8\n",
      "year_2018    171162 non-null uint8\n",
      "month_1      171162 non-null uint8\n",
      "month_2      171162 non-null uint8\n",
      "month_3      171162 non-null uint8\n",
      "month_4      171162 non-null uint8\n",
      "month_5      171162 non-null uint8\n",
      "month_6      171162 non-null uint8\n",
      "month_7      171162 non-null uint8\n",
      "month_8      171162 non-null uint8\n",
      "month_9      171162 non-null uint8\n",
      "month_10     171162 non-null uint8\n",
      "month_11     171162 non-null uint8\n",
      "month_12     171162 non-null uint8\n",
      "dtypes: float64(13), uint8(20)\n",
      "memory usage: 20.7+ MB\n"
     ]
    }
   ],
   "source": [
    "returns.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get stock characteristics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create age proxy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.216013Z",
     "start_time": "2018-11-09T16:12:38.212117Z"
    }
   },
   "outputs": [],
   "source": [
    "stocks['age'] = pd.qcut(stocks.ipoyear, q=5, labels=list(range(1, 6))).astype(float).fillna(0).astype(int)\n",
    "stocks = stocks.drop('ipoyear', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create size proxy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2412 entries, A to ZUMZ\n",
      "Data columns (total 3 columns):\n",
      "marketcap    2407 non-null float64\n",
      "sector       2372 non-null object\n",
      "age          2412 non-null int64\n",
      "dtypes: float64(1), int64(1), object(1)\n",
      "memory usage: 155.4+ KB\n"
     ]
    }
   ],
   "source": [
    "stocks.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ticker\n",
       "A       1.960000e+10\n",
       "AA      8.540000e+09\n",
       "AAL     1.767000e+10\n",
       "AAMC    1.104900e+08\n",
       "AAN     3.200000e+09\n",
       "Name: marketcap, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.marketcap.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.352174Z",
     "start_time": "2018-11-09T16:12:38.240412Z"
    }
   },
   "outputs": [],
   "source": [
    "stocks['size'] = pd.qcut(stocks.marketcap, q=10, labels=list(range(1, 11)))\n",
    "stocks = stocks.drop(['marketcap'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create Dummy variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.357464Z",
     "start_time": "2018-11-09T16:12:38.353287Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2412 entries, A to ZUMZ\n",
      "Data columns (total 3 columns):\n",
      "sector    2372 non-null object\n",
      "age       2412 non-null int64\n",
      "size      2407 non-null category\n",
      "dtypes: category(1), int64(1), object(1)\n",
      "memory usage: 139.3+ KB\n"
     ]
    }
   ],
   "source": [
    "stocks.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:38.369633Z",
     "start_time": "2018-11-09T16:12:38.358581Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2412 entries, A to ZUMZ\n",
      "Data columns (total 28 columns):\n",
      "size_1                   2412 non-null uint8\n",
      "size_2                   2412 non-null uint8\n",
      "size_3                   2412 non-null uint8\n",
      "size_4                   2412 non-null uint8\n",
      "size_5                   2412 non-null uint8\n",
      "size_6                   2412 non-null uint8\n",
      "size_7                   2412 non-null uint8\n",
      "size_8                   2412 non-null uint8\n",
      "size_9                   2412 non-null uint8\n",
      "size_10                  2412 non-null uint8\n",
      "age_0                    2412 non-null uint8\n",
      "age_1                    2412 non-null uint8\n",
      "age_2                    2412 non-null uint8\n",
      "age_3                    2412 non-null uint8\n",
      "age_4                    2412 non-null uint8\n",
      "age_5                    2412 non-null uint8\n",
      "Basic Industries         2412 non-null uint8\n",
      "Capital Goods            2412 non-null uint8\n",
      "Consumer Durables        2412 non-null uint8\n",
      "Consumer Non-Durables    2412 non-null uint8\n",
      "Consumer Services        2412 non-null uint8\n",
      "Energy                   2412 non-null uint8\n",
      "Finance                  2412 non-null uint8\n",
      "Health Care              2412 non-null uint8\n",
      "Miscellaneous            2412 non-null uint8\n",
      "Public Utilities         2412 non-null uint8\n",
      "Technology               2412 non-null uint8\n",
      "Transportation           2412 non-null uint8\n",
      "dtypes: uint8(28)\n",
      "memory usage: 164.8+ KB\n"
     ]
    }
   ],
   "source": [
    "stocks = pd.get_dummies(stocks, \n",
    "                        columns=['size', 'age',  'sector'], \n",
    "                        prefix=['size', 'age', ''], \n",
    "                        prefix_sep=['_', '_', ''])\n",
    "stocks.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:41.045331Z",
     "start_time": "2018-11-09T16:12:38.370654Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "MultiIndex: 171162 entries, (A, 2011-03-31 00:00:00) to (ZUMZ, 2018-02-28 00:00:00)\n",
      "Data columns (total 61 columns):\n",
      "returns                  171162 non-null float64\n",
      "t-1                      171162 non-null float64\n",
      "t-2                      171162 non-null float64\n",
      "t-3                      171162 non-null float64\n",
      "t-4                      171162 non-null float64\n",
      "t-5                      171162 non-null float64\n",
      "t-6                      171162 non-null float64\n",
      "t-7                      171162 non-null float64\n",
      "t-8                      171162 non-null float64\n",
      "t-9                      171162 non-null float64\n",
      "t-10                     171162 non-null float64\n",
      "t-11                     171162 non-null float64\n",
      "t-12                     171162 non-null float64\n",
      "year_2011                171162 non-null uint8\n",
      "year_2012                171162 non-null uint8\n",
      "year_2013                171162 non-null uint8\n",
      "year_2014                171162 non-null uint8\n",
      "year_2015                171162 non-null uint8\n",
      "year_2016                171162 non-null uint8\n",
      "year_2017                171162 non-null uint8\n",
      "year_2018                171162 non-null uint8\n",
      "month_1                  171162 non-null uint8\n",
      "month_2                  171162 non-null uint8\n",
      "month_3                  171162 non-null uint8\n",
      "month_4                  171162 non-null uint8\n",
      "month_5                  171162 non-null uint8\n",
      "month_6                  171162 non-null uint8\n",
      "month_7                  171162 non-null uint8\n",
      "month_8                  171162 non-null uint8\n",
      "month_9                  171162 non-null uint8\n",
      "month_10                 171162 non-null uint8\n",
      "month_11                 171162 non-null uint8\n",
      "month_12                 171162 non-null uint8\n",
      "size_1                   171162 non-null int8\n",
      "size_2                   171162 non-null int8\n",
      "size_3                   171162 non-null int8\n",
      "size_4                   171162 non-null int8\n",
      "size_5                   171162 non-null int8\n",
      "size_6                   171162 non-null int8\n",
      "size_7                   171162 non-null int8\n",
      "size_8                   171162 non-null int8\n",
      "size_9                   171162 non-null int8\n",
      "size_10                  171162 non-null int8\n",
      "age_0                    171162 non-null int8\n",
      "age_1                    171162 non-null int8\n",
      "age_2                    171162 non-null int8\n",
      "age_3                    171162 non-null int8\n",
      "age_4                    171162 non-null int8\n",
      "age_5                    171162 non-null int8\n",
      "Basic Industries         171162 non-null int8\n",
      "Capital Goods            171162 non-null int8\n",
      "Consumer Durables        171162 non-null int8\n",
      "Consumer Non-Durables    171162 non-null int8\n",
      "Consumer Services        171162 non-null int8\n",
      "Energy                   171162 non-null int8\n",
      "Finance                  171162 non-null int8\n",
      "Health Care              171162 non-null int8\n",
      "Miscellaneous            171162 non-null int8\n",
      "Public Utilities         171162 non-null int8\n",
      "Technology               171162 non-null int8\n",
      "Transportation           171162 non-null int8\n",
      "dtypes: float64(13), int8(28), uint8(20)\n",
      "memory usage: 25.3+ MB\n"
     ]
    }
   ],
   "source": [
    "data = (returns\n",
    "        .reset_index('date')\n",
    "        .merge(stocks, left_index=True, right_index=True)\n",
    "        .dropna()\n",
    "        .set_index('date', append=True))\n",
    "\n",
    "s = len(returns.columns)\n",
    "data.iloc[:, s:] = data.iloc[:, s:].astype(int).apply(pd.to_numeric, downcast='integer')\n",
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-09T16:12:41.079760Z",
     "start_time": "2018-11-09T16:12:41.046398Z"
    }
   },
   "outputs": [],
   "source": [
    "with pd.HDFStore('data.h5') as store:\n",
    "    store.put('data', data)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
